#!/bin/python
#coding=utf8
import MySQLdb
import datetime
from ftplib import FTP
from hynmath import loadConfig,saveConfig
import commands
import os
import sys

HERE = sys.path[0]
os.chdir(HERE)

# 加载配置参数
config = {}
loadConfig(config,'config.txt')
#config['database'] = 'bestv'
tmp_startDate = config['startDate'].split('-')
startDate = datetime.date(int(tmp_startDate[0]),int(tmp_startDate[1]),int(tmp_startDate[2]))

ftp_ip = '124.108.10.55'
ftp_user = 'ftp_orderlog1'
ftp_password = 'ftp_orderlog1'
ftp_path = 'Guangzhou'

# 创建数据库连接
connection = MySQLdb.connect(host=config['host'], user=config['user'], passwd=config['password'], db=config['database'], init_command=config['encoding'])
cursor = connection.cursor()

# 创建 FTP 连接
ftp = FTP(ftp_ip,ftp_user,ftp_password)
ftp.cwd(ftp_path)
filesList = ftp.nlst()

# 逐个文件导入数据库
i = 0
while 1:
  i += 1
  procDate = startDate + datetime.timedelta(days = i)
  tmp_procDate = str(procDate).split('-')
  target = ''.join(tmp_procDate)
  
  targetFileName = ''
  for fileName in filesList:
    if fileName.find(target) >= 0:
      targetFileName = fileName
      break

  if targetFileName == '':
    break
  else:
    config['startDate'] = procDate
    cmd = 'wget ftp://%s:%s@%s/Guangzhou/%s' % (ftp_user,ftp_password,ftp_ip,targetFileName)
    os.system(cmd)
    cmd = 'unzip %s' % targetFileName
    os.system(cmd)
    # 获取旧的文本文件名
    cmd = 'ls *%s.log' % target
    txtFileName = commands.getoutput(cmd).split('\n')[0]
    print txtFileName
    sql = "truncate table order_dat;"
    cursor.execute(sql)
    sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE order_dat character set gbk FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;" % txtFileName
    cursor.execute(sql)
#    sql = "update order_dat set UnsubscribeTime=NULL where UnsubscribeTime='0000-00-00 00:00:00';"
    sql = "call sp_ImportOrderData();"
    cursor.execute(sql)
    sql = "call sp_CalcOrderReport(%s,1);" % procDate
    cursor.execute(sql)
    sql = "insert into ksdailyreports select date(StartTime) as UPTIME,ABGROUP,CLASS,TYPE,COUNT,FEE from ksdailyreport;"
    cursor.execute(sql)
#    sql = "insert into order_info select UserId,OrderId,OrderValid,OrderType,MediaCode,ContentName,ServiceName,ProductId,ProductName,Fee,SubscribeTime,UnsubscribeTime,ValideTime,ExpiredTime,UpdateTime,PlatformName from order_dat;"
#    cursor.execute(sql)
    
    os.remove(txtFileName)
    os.remove(targetFileName)
    
    

saveConfig(config,'config.txt')

